from faker import Faker
import random
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from data_mock.utils import FileUtil, MysqlUtils_AI

fake = Faker('zh_CN')

# ========== 配置基础数据 ==========
GENDERS = ['男', '女']
VISIT_TYPES = ['门诊', '住院', '急诊']
OCCUPATIONS = ['教师', '医生', '护士', '工程师', '学生', '农民']
NATIONALITIES = ['中国', '美国', '英国', '日本']
ETHNICITIES = ['汉族', '回族', '满族', '藏族', '维吾尔族']
EDUCATIONS = ['小学', '初中', '高中', '本科', '硕士', '博士']
MARITAL_STATUSES = ['未婚', '已婚', '离异', '丧偶']
CERTIFICATE_TYPES = ['身份证', '护照', '军官证']
INSURANCE_TYPES = ['甲类医保', '乙类医保', '自费', '特殊药品']
BLOOD_TYPES_ABO = ['A', 'B', 'AB', 'O']
BLOOD_TYPES_RH = ['阴性', '阳性']


# ========== 时间范围配置 ==========
def generate_DAY_RANGE(start_day, end_day):
    start = datetime.strptime(start_day, "%Y-%m-%d")
    end = datetime.strptime(end_day, "%Y-%m-%d")
    days = []
    current = start
    while current <= end:
        days.append(current.strftime("%Y-%m-%d"))
        current += relativedelta(days=1)
    return days


DAY_RANGE = generate_DAY_RANGE("2025-08-01", "2025-08-31")


def generate_records_per_day(day: str):
    date_obj = datetime.strptime(day, '%Y-%m-%d')
    return 150 if date_obj.day % 2 == 0 else 120


# ========== 核心生成函数 ==========
def generate_patient_records():
    sql_statements = []
    record_count = 0

    patient_id_counter = 1001  # patient_id 从 PT1001 开始
    visit_sn_counter = 1001
    for day in DAY_RANGE:
        RECORDS_PER_day = generate_records_per_day(day)
        for i in range(1, RECORDS_PER_day + 1):
            record_count += 1
            patient_id = f"PT{patient_id_counter}"
            patient_id_counter += 1

            visit_sn = f"VIS{visit_sn_counter}"
            visit_sn_counter += 1

            visit_type = random.choice(VISIT_TYPES)
            visit_times = random.randint(1, 5)
            outpatient_no = f"OUT{random.randint(1000, 9999)}"
            visit_datetime = fake.date_time_between(
                start_date=datetime.strptime(day, "%Y-%m-%d"),
                end_date=(datetime.strptime(day, "%Y-%m-%d") + timedelta(days=1))
            ).strftime('%Y-%m-%d %H:%M:%S')
            medical_record_no = f"MR{random.randint(100000, 999999)}"
            inpatient_no = f"IP{random.randint(100000, 999999)}"
            hospitalization_times = str(random.randint(1, 3))
            admission_datetime = (datetime.strptime(day, "%Y-%m-%d") - timedelta(days=random.randint(0, 5))).strftime(
                '%Y-%m-%d %H:%M:%S')
            discharge_datetime = (datetime.strptime(day, "%Y-%m-%d") + timedelta(days=random.randint(0, 5))).strftime(
                '%Y-%m-%d %H:%M:%S')
            visit_doctor_no = f"DOC{random.randint(100, 999)}"
            name = fake.name()
            gender = random.choice(GENDERS)
            date_of_birth = fake.date_of_birth(minimum_age=1, maximum_age=90).strftime('%Y-%m-%d')
            occupation_name = random.choice(OCCUPATIONS)
            nationality = random.choice(NATIONALITIES)
            ethnicity = random.choice(ETHNICITIES)
            education = random.choice(EDUCATIONS)
            marital_status = random.choice(MARITAL_STATUSES)
            certificate_type = random.choice(CERTIFICATE_TYPES)
            certificate_no = str(fake.random_int(100000000000000000, 999999999999999999))
            insurance_type = random.choice(INSURANCE_TYPES)
            insurance_no = f"INS{random.randint(1000000, 9999999)}"
            residence_province = fake.province()
            residence_city = fake.city()
            residence_county = fake.country()
            current_address = fake.address().replace("\n", " ")
            phone_no = fake.phone_number()
            abo_blood_type = random.choice(BLOOD_TYPES_ABO)
            rh_blood_type = random.choice(BLOOD_TYPES_RH)
            record_status = random.randint(0, 1)
            yy_batch_time = day
            yy_record_batch_id = f"BATCH{day}_{i}"
            yy_etl_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

            data = {
                'patient_id': patient_id,
                'visit_sn': visit_sn,
                'visit_type': visit_type,
                'outpatient_no': outpatient_no,
                'visit_times': visit_times,
                'visit_datetime': visit_datetime,
                'medical_record_no': medical_record_no,
                'inpatient_no': inpatient_no,
                'hospitalization_times': hospitalization_times,
                'admission_datetime': admission_datetime,
                'discharge_datetime': discharge_datetime,
                'visit_doctor_no': visit_doctor_no,
                'name': name,
                'gender': gender,
                'date_of_birth': date_of_birth,
                'occupation_name': occupation_name,
                'nationality': nationality,
                'ethnicity': ethnicity,
                'education': education,
                'marital_status': marital_status,
                'certificate_type': certificate_type,
                'certificate_no': certificate_no,
                'insurance_type': insurance_type,
                'insurance_no': insurance_no,
                'residence_province': residence_province,
                'residence_city': residence_city,
                'residence_county': residence_county,
                'current_address': current_address,
                'phone_no': phone_no,
                'abo_blood_type': abo_blood_type,
                'rh_blood_type': rh_blood_type,
                'record_status': record_status,
                'yy_etl_time': yy_etl_time,
                'yy_upload_status': 0,
                'yy_upload_time': None,
                'yy_batch_time': yy_batch_time,
                'yy_record_batch_id': yy_record_batch_id,
                'yy_backfill_time': None,
                'yy_backfill_status': None
            }

            sql = _generate_sql('b02_1', data)
            sql_statements.append(sql)

    return sql_statements


def _generate_sql(table, data):
    columns = ', '.join([f'`{k}`' for k in data.keys()])
    values = []
    for v in data.values():
        if v is None:
            values.append('NULL')
        elif isinstance(v, (int, float)):
            values.append(str(v))
        else:
            escaped_value = str(v).replace("'", "''")
            values.append(f"'{escaped_value}'")
    return f"INSERT INTO `{table}` ({columns}) VALUES ({', '.join(values)});"


# ========== 执行生成 ==========
if __name__ == "__main__":
    records = generate_patient_records()
    # 写入数据库
    MysqlUtils_AI.insert_data_to_hub(records, 'b02_1')
    # 或写入 SQL 文件
    # FileUtil.generate_sql_file(records, "b02_1_patient_records.sql")
